package com.bbs.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.bbs.entity.Article;
import com.bbs.entity.Blocks;
import com.bbs.entity.Reply;
import com.bbs.entity.User;
import com.bbs.util.DBHelp;
import com.bbs.util.DateUtil;
import com.bbs.util.Page;
import com.bbs.util.RowMapper;

public class ReplyDao {
	private DBHelp<Reply> db = new DBHelp<Reply>();
	
	/**
	 * 保存帖子
	 * @param reply
	 * @return
	 */
	public int save(Reply reply) {
		String sql = "INSERT INTO t_reply (content, createdate, t_user_id, t_article_id, pid, state) VALUES(?,?,?,?,?,?)";
		return db.update(sql,  reply.getContent(), reply.getCreatedate(), reply.getUser().getId(), reply.getArticle().getId(), reply.getPid(), reply.isState());
	}
	
	/**
	 * 根据articleid返回回帖list
	 * @param articleid
	 * @return
	 */
	public List<Reply> findByAtricleId(int articleid) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT r.id, r.content, r.createdate, r.t_user_id AS userid, r.t_article_id AS articleid, r.pid, r.state,");
		sql.append("u.id AS userid, u.username, u.userpwd, u.score, u.LEVEL, u.headpic, u.createdate AS usercreatedate,");
		sql.append("u.email, u.role, u.state, u.active FROM t_reply AS r LEFT JOIN t_user AS u ON r.t_user_id = u.id ");
		sql.append("WHERE r.t_article_id = ?");
		
		
		return db.queryForList(sql.toString(), new ReplyMapper(), articleid);
	}

	public void deleteByAid(int id) {
		String sql = "DELETE FROM t_reply WHERE t_article_id = ?";
		db.update(sql, id);
	}

	public List<Reply> findByAtricleIdAndPage(int id, Page page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT r.id, r.content, r.createdate, r.t_user_id AS userid, r.t_article_id AS articleid, r.pid, r.state,");
		sql.append("u.id AS userid, u.username, u.userpwd, u.score, u.LEVEL, u.headpic, u.createdate AS usercreatedate,");
		sql.append("u.email, u.role, u.state, u.active FROM t_reply AS r LEFT JOIN t_user AS u ON r.t_user_id = u.id ");
		sql.append("WHERE r.t_article_id = ? ");
		sql.append("limit ?, ? ");
		
		return db.queryForList(sql.toString(), new ReplyMapper(), id, page.getStartNum(), page.getPageSize());
	}

	public int findReplyCount(int articleid) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) ");
		sql.append("FROM t_reply ");
		sql.append("LEFT JOIN t_article ON t_article.id = t_reply.t_article_id ");
		sql.append("WHERE t_article.id = ? ");
		
		
		return db.queryForCount(sql.toString(), articleid);
	}

	public List<Reply> findMineByUid(int id,Page page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT r.id,r.content,r.createdate,r.t_user_id,t_article_id,r.pid,r.state,");
		sql.append("u.id AS userid,u.username,u.userpwd,u.score,u.level,u.headpic,u.createdate AS usercreatedate,");
		sql.append("u.email,u.role,u.state,u.active,a.id AS articleid,a.name AS articlename,a.content AS articlecontent,");
		sql.append("a.createdate AS articlecreatedate,a.state AS articlestate,a.t_user_id AS articleuserid,t_blocks_id,a.clicknum,a.type,a.settoptime,");
		sql.append("b.id AS blocksid,b.name AS blocksname,b.pid AS blockspid,b.content AS blockscontent FROM t_reply AS r  ");
		sql.append("LEFT JOIN t_user AS u ON r.t_user_id = u.id LEFT JOIN t_article AS a ON t_article_id = a.id ");
		sql.append("LEFT JOIN t_blocks AS b ON t_blocks_id = b.id  WHERE r.t_user_id = ? order by(r.createdate) desc LIMIT ?,?");
		return db.queryForList(sql.toString(), new RowMapper<Reply>(){
			public Reply maprow(ResultSet rs) throws SQLException {
				Reply r = new  Reply();
				r.setContent(rs.getString("content"));
				r.setCreatedate(DateUtil.friendlyTime(DateUtil.stringToDate(rs.getString("createdate"))));
				r.setId(rs.getInt("id"));
				r.setPid(rs.getInt("pid"));
				r.setState(rs.getBoolean("state"));
				
				User u = new User();
				u.setActive(rs.getInt("active"));
				u.setCreatedate(rs.getString("usercreatedate"));
				u.setEmail(rs.getString("email"));
				u.setHeadpic(rs.getString("headpic"));
				u.setId(rs.getInt("userid"));
				u.setLevel(rs.getInt("level"));
				u.setRole(rs.getInt("role"));
				u.setScore(rs.getInt("score"));
				u.setState(rs.getBoolean("state"));
				u.setUsername(rs.getString("username"));
				u.setUserpwd(rs.getString("userpwd"));
				r.setUser(u);
				
				Article a = new Article();
				a.setClickNum(rs.getInt("clickNum"));
				a.setContent(rs.getString("articlecontent"));
				a.setCreatedate(DateUtil.friendlyTime(DateUtil.stringToDate(rs.getString("articlecreatedate"))));
				a.setId(rs.getInt("articleid"));
				a.setName(rs.getString("articlename"));
				a.setSettoptime(rs.getString("settoptime"));
				a.setState(rs.getBoolean("articlestate"));
				a.setType(rs.getInt("type"));
				a.setUser(new Userdao().findById(rs.getInt("articleuserid")));
				
				Blocks b = new Blocks();
				b.setContent(rs.getString("blockscontent"));
				b.setId(rs.getInt("blocksid"));
				b.setName(rs.getString("blocksname"));
				b.setPid(rs.getInt("blockspid"));
				a.setBlocks(b);
				r.setArticle(a);
				return r;
			}
		}, id,page.getStartNum(),page.getPageSize());
	}

	public int getCountByuid(int id) {
		String sql = "SELECT COUNT(*) FROM t_reply WHERE t_user_id = ?";
		return db.queryForCount(sql, id);
	}
}

class ReplyMapper implements RowMapper<Reply> {
	public Reply maprow(ResultSet rs) throws SQLException {
		Reply reply = new Reply();
		reply.setId(rs.getInt("id"));
		reply.setContent(rs.getString("content"));
		reply.setCreatedate(rs.getString("createdate"));
		reply.setPid(rs.getInt("pid"));
		reply.setState(rs.getBoolean("state"));
		
		User u = new User();
		u.setActive(rs.getInt("active"));
		u.setCreatedate(rs.getString("usercreatedate"));
		u.setEmail(rs.getString("email"));
		u.setHeadpic(rs.getString("headpic"));
		u.setId(rs.getInt("userid"));
		u.setLevel(rs.getInt("level"));
		u.setRole(rs.getInt("role"));
		u.setScore(rs.getInt("score"));
		u.setUsername(rs.getString("username"));
		u.setUserpwd(rs.getString("userpwd"));
		
		reply.setUser(u);
		return reply;
	}
	
}